# -*- coding: utf-8 -*-

import sqlite3
from datetime import date

class DBStock(object):
	"""
	manager stock db
	"""
	def __init__(self,db_connect):
		self.db = db_connect
		self.cu = self.db.cursor()

	def fetch_hist_tb_name(self):
		self.cu.execute('select code from stock_classify where key like "%_a"')
		return [row[0] for row in self.cur.fetchall()]

	def check_code_table(self,code):
		sql = "create table if not exists his_%s"\
			" (date date PRIMARY KEY NOT NULL,"\
			"open float,upper float,lower float,close float,vol double,amount double) "%(code)
		ret = self.cu.execute(sql)

	def check_date(self,code,dt):
		sql = "select * from his_%s where date = '%s'"%(code,dt)
		self.cu.execute(sql)
		return len(self.cu.fetchall())

	def check_tb_stock_classify(self):
		sql = "create table if not exists stock_classify"\
			" (key VARCHAR(50),code VARCHAR(30) PRIMARY KEY NOT NULL)"
		ret = self.cu.execute(sql)

	def check_tb_stock_csrc_industry(self):		
		sql = "create table if not exists stock_csrc_industry"\
			" (key1 VARCHAR(50),key2 VARCHAR(50),code VARCHAR(30) PRIMARY KEY NOT NULL)"
		ret = self.cu.execute(sql)

	def check_tb_stock_sina_industry(self):	
		sql = "create table if not exists stock_sina_industry"\
			" (key VARCHAR(50),code VARCHAR(30) PRIMARY KEY NOT NULL)"
		ret = self.cu.execute(sql)

	def check_tb_stock_info(self):
		sql = "create table if not exists stock_info"\
			" (code VARCHAR(30) PRIMARY KEY NOT NULL,"\
			"name VARCHAR(70),symbol VARCHAR(50),begin_date date)"
		ret = self.cu.execute(sql)

	def insert_stock_classify(self,data):
		sql = "select * from stock_classify where code = '%s'"%(data[1])
		self.cu.execute(sql)
		if len(self.cu.fetchall()) > 0:
			return
		sql = "insert into stock_classify values('%s','%s')"\
			%(data[0],data[1])
		self.cu.execute(sql)
		self.db.commit()

	def insert_stock_info(self,stock_info):
		sql = "select * from stock_info where code = '%s'"%(stock_info[0])
		self.cu.execute(sql)
		if len(self.cu.fetchall()) > 0:
			return
		sql = "insert into stock_info values('%s','%s','%s','')"\
			%(stock_info[0],stock_info[1],stock_info[2])
		self.cu.execute(sql)
		self.db.commit()

	def insert_stock_daily(self,stock_daily):
		dt = stock_daily[1]
		code_ = stock_daily[0]		
		self.check_code_table(code_)
		if self.check_date(code_,dt) > 0:
			return False
		sql = "insert into his_%s values('%s',%s,%s,%s,%s,%s,%s)"\
			%tuple(stock_daily)
		self.cu.execute(sql)
		self.db.commit()
		return True

	def save_his_stock(self,item):
		code_ = item['item_code']
		data = item['data']
		index = 0
		for row in data:
			daily_info = [code_,row[0],row[1],row[2],row[3],row[4],row[5],row[6]]
			if self.insert_stock_daily(daily_info):
				index += 1
		print "insert into his_",code_," ",index," rows"

	def save_stock_classify(self,item):
		self.check_tb_stock_classify()
		self.check_tb_stock_info()
		classify_name_ = item['item_code']
		print "save_stock_classify,",classify_name_
		data = item['data']
		for row in data:
			classify_info = [classify_name_,row['code']]
			self.insert_stock_classify(classify_info)
			stock_info = [row['code'],row['name'],row['symbol']]
			self.insert_stock_info(stock_info)
	def save_daily_analyze_result(self,str_date,data):
		pass